using System;
using System.Data.Common;
#if SQLITE
using System.Data.SQLite;
#endif
using System.Collections.Generic;

namespace RikMigrations.Providers
{
#if SQLITE
	public class SqliteProvider : DbProvider
	{
		#region Basic Commands
		protected override DbConnection CreateNewConnection()
		{
			DbConnection retVal;
			retVal = new SQLiteConnection(ConnectionString);
			retVal.Open();
			return retVal;
		}

		protected override DbParameter CreateParameter(int i, object value)
		{
			return new SQLiteParameter("@" + i, value);
		}
		#endregion

		#region Introspection
		public override bool TableExists(string tableName)
		{
			return ExecuteScalar("SELECT * FROM sqlite_master WHERE	type = 'view' AND name = ?", tableName) != null;
		}

        public override bool ViewExists(string tableName)
        {
            return ExecuteScalar("SELECT * FROM sqlite_master WHERE	type = 'table' AND name = ?", tableName) != null;
        }

        public override bool StoredProcedureExists(string name)
        {
            throw new NotImplementedException();
        }

		public override bool ColumnExists(string tableName, string name)
		{
			using (DbDataReader reader = ExecuteReader("PRAGMA table_info(" + tableName + ")"))
				while (reader.Read())
				{
					string colName = reader.GetString(1);
					if (colName == name)
						return true;
				}
			return false;
		}

		public override List<ForeignKey> FindForeignKeys(Table table)
		{
			throw new NotImplementedException("The method or operation is not implemented.");
		}
		#endregion

		#region Migration Methods
		public override void DropTable(string name)
		{
			ExecuteNonQuery("DROP TABLE [" + name + "]");
		}

        public override void DropView(string name)
        {
            ExecuteNonQuery("DROP VIEW [" + name + "]");
        }
		#endregion

		public override int GetModuleVersion(string moduleName)
		{
			MigrationManager.Initialise(this);
			object o = ExecuteScalar("SELECT CurrentVersion FROM ModuleVersions WHERE ModuleName = @0", moduleName);
			return o == null ? 0 : (int)o;
		}

		public override void SetModuleVersion(string moduleName, int version)
		{
			MigrationManager.Initialise(this);
			object o = ExecuteScalar("SELECT CurrentVersion FROM ModuleVersions WHERE ModuleName = @0", moduleName);
			if (o == null)
				ExecuteNonQuery("INSERT INTO ModuleVersions (ModuleName, CurrentVersion) VALUES (@0, @1)", moduleName, version);
			else
				ExecuteNonQuery("UPDATE ModuleVersions SET CurrentVersion = @0 WHERE ModuleName = @1", version, moduleName);
		}

		#region Commands
		public override Command GetCreateTableCommand(Table table)
		{
			return new SqliteCreateTableCommand(table);
		}

		public override Command GetAddColumnCommand(Column column)
		{
			return new SqliteAddColumnCommand(column);
		}

		public override Command GetDropColumnCommand(Table table, string columnName)
		{
			throw new NotImplementedException("Sqlite does not support dropping columns. Workarounds exist but have not been implemented in RikMigrations.");
		}

        public override Command GetInsertCommand(string tableName, object objectToInsert, bool IsIdentityInsert)
        {
            throw new NotImplementedException();
        }

        public override Command GetCreateViewCommand(View view)
        {
            throw new NotImplementedException();
        }

        public override Command GetCreateSPCommand(StoredProcedure sp)
        {
            throw new NotImplementedException();
        }

		#region Table Commands
		class SqliteCreateTableCommand : Command
		{
			Table table;

			public SqliteCreateTableCommand(Table table)
			{
				this.table = table;
			}

			public override void Execute(DbProvider db)
			{
				try
				{
					SqliteProvider sqlDb = db as SqliteProvider;

					string sql = "CREATE TABLE [" + table.Name + "] (";
					foreach (Column col in table.Columns)
						sql += sqlDb.GetColumnSql(col) + ",\r\n";
					foreach (Constraint constraint in table.Constraints)
						sql += Providers.SqliteProvider.GetConstraintSql(constraint) + ",\r\n";
					sql = sql.Substring(0, sql.Length - 3);
					sql += ")";
					db.ExecuteNonQuery(sql);
				}
				catch (SQLiteException e)
				{
					throw new Exception("Error creating table: " + table.Name, e);
				}
			}
		}
		#endregion

		#region Column Commands
		internal class SqliteAddColumnCommand : Command
		{
			Column column;

			public SqliteAddColumnCommand(Column column)
			{
				this.column = column;
			}

			public override void Execute(DbProvider db)
			{
				try
				{
					SqliteProvider sqlDb = db as SqliteProvider;
					string sql = sqlDb.GetColumnSql(column);
					db.ExecuteNonQuery("ALTER TABLE " + column.Table.Name + " ADD " + sql);
				}
				catch (SQLiteException e)
				{
					throw new Exception("Error adding column: " + column.Name, e);
				}
			}
		}
		#endregion

		public override Command GetAddForeignKeyCommand(ForeignKey key) { throw new NotImplementedException(); }
		public override Command GetRenameTableCommand(Column column, string newName) { throw new NotImplementedException(); }
		#endregion

		#region Utilities
		public string GetColumnSql(Column column)
		{
			string sql = string.Format("[{1}] {2}", column.Table.Name, column.Name, column.SqlType ?? GetSqlType(column.Type, column.Length, column.IsBlob, column.IsUnicode));
			if (column.IsPrimaryKey)
			{
				sql += " PRIMARY KEY";
				if (column.IsAutoGenerated)
					sql += " AUTOINCREMENT";
			}
			if (!column.AllowNull)
				sql += " NOT NULL";
			if (column.IsUnique)
				sql += " UNIQUE";
			if (column.DefaultValue != null)
				sql += " DEFAULT " + GetLiteral(column.Type, column.DefaultValue);
			
			if (column.ExtraOptions != null)
				sql += " " + column.ExtraOptions;
			return sql;
		}

		public static string GetSqlType(Type type, int length, bool isBlob, bool isUnicode)
		{
			if (type == typeof(bool) || type == typeof(Guid) || type == typeof(DateTime))
				return "TEXT";
			else if (type == typeof(string) || type == typeof(char))
				return "TEXT";
			else if (type == typeof(byte) || type == typeof(short) || type == typeof(int) || type == typeof(long))
				return "INT";
			else if (type == typeof(double) || type == typeof(float) || type == typeof(decimal))
				return "REAL";
			else if (type == typeof(byte[]))
				return "BLOB";
			else
				return "TEXT"; // Sqlite doesn't really have types anyway so just let them throw whatever they want in
		}

		public static string GetConstraintSql(Constraint constraint)
		{
			string sql;
			switch (constraint.Type)
			{
				case ConstraintType.PrimaryKey:
					sql = string.Format("CONSTRAINT [{0}] PRIMARY KEY (", constraint.Name);
					break;
				case ConstraintType.Unique:
					sql = string.Format("CONSTRAINT [{0}] UNIQUE (", constraint.Name);
					break;
				default:
					throw new NotImplementedException();
			}
			foreach (string col in constraint.Columns)
				sql += col + ",";
			return sql.Substring(0, sql.Length - 1) + ")";
		}
		#endregion

    }
#endif
}